IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GetServerListForGetServerInfo' AND type = 'P')
     DROP PROCEDURE dbo.GetServerListForGetServerInfo
GO

CREATE PROCEDURE dbo.GetServerListForGetServerInfo
     @SrvrInstncGrpId int = NULL 
AS
/*
 *
 * GetServerListForGetServerInfo
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 12/22/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     -- Log sproc use.  Starting
     EXEC dbo.InsEventLog @EventLogTypeId = 1, -- Informational
                          @LogByApp       = 'GetServerListForGetServerInfo',
                          @LogText        = 'GetServerListForGetServerInfo Starting',
                          @MiscInt1       = @SrvrInstncGrpId

     DECLARE @RC int 

     -- Condition SrvrInstncGrpId 
     SELECT @SrvrInstncGrpId = CASE WHEN @SrvrInstncGrpId IS NULL
                                    THEN NULL
                                    WHEN @SrvrInstncGrpId = 0
                                    THEN NULL
                                    WHEN @SrvrInstncGrpId < 0
                                    THEN NULL
                                    WHEN @SrvrInstncGrpId > 0
                                    THEN @SrvrInstncGrpId
                               END

     -- Verify Group
     IF   ( @SrvrInstncGrpId IS NOT NULL
        AND NOT EXISTS ( SELECT 'X'
                         FROM   dbo.SrvrInstncGrp sig
                         WHERE  sig.SrvrInstncGrpId = @SrvrInstncGrpId )
          )
     BEGIN
          EXEC dbo.InsEventLog @EventLogTypeId = 3, -- Error
                               @LogByApp       = 'GetServerListForGetServerInfo',
                               @LogText        = 'GroupId was not found.',
                               @MiscInt1       = @SrvrInstncGrpId
          RAISERROR( 'GroupId was not found.', 16, 1 )
     END
     ELSE
     BEGIN
          -- Return the results.
          SELECT SrvrInstncId,
                 SrvrInstncTypeId,
                 SrvrInstncFullName
          FROM   dbo.SrvrInstnc si
          WHERE  si.EnabledFlag = 1
          AND  ( si.SrvrInstncGrpId = @SrvrInstncGrpId
              OR @SrvrInstncGrpId   IS NULL )

          SELECT @RC = @@ROWCOUNT
     END
     
     -- Log ending..
     EXEC dbo.InsEventLog @EventLogTypeId = 1, -- Informational
                          @LogByApp       = 'GetServerListForGetServerInfo',
                          @LogText        = 'GetServerListForGetServerInfo Ending',
                          @MiscInt1       = @SrvrInstncGrpId,
                          @MiscInt2       = @RC
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Returns a list of servers for the GetServerInfo package.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = GetServerListForGetServerInfo;
GO
